﻿
namespace GSBA.DataImporter
{
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Net;
    using System.Text.RegularExpressions;
    using System.Web;
    using System.Xml;

    class Program
    {
        static void Main(string[] args)
        {
            const string filename = @"C:\Users\chrixx\Projects\GSBA\data\20130621125207_WindowsAppData.zip";

            var archive = DataArchive.Open(filename);
            foreach (var item in archive.Listings)
            {
                if (item.Longitude == 0.0 && item.Latitude == 0.0 && item.ZipCode != null)
                {
                    var zipIndex = item.ZipCode.IndexOf('-');
                    zipIndex = zipIndex > 0 ? zipIndex : item.ZipCode.Length;

                    Regex regex = new Regex("^\\d+$");
                    var addressLines = string.Empty;
                    var addressLineItems = item.Line1.Split(new char[]{ ' ' });
                    if (regex.IsMatch(addressLineItems[0]))
                    {
                        addressLines += item.Line1;
                    }
                    if (item.Line2 != null)
                    {
                        addressLineItems = item.Line2.Split(new char[] { ' ' });
                        if (regex.IsMatch(addressLineItems[0]))
                        {
                            addressLines += " " + item.Line2;
                        }
                    }
                    if (item.Line3 != null)
                    {
                        var line3 = item.Line3;
                        if (item.Line3.StartsWith("MAILING"))
                        {
                            line3 = item.Line3.Substring(item.Line3.IndexOf(' '));
                        }
                        if (item.Line3.Contains('#'))
                        {
                            line3 = line3.Substring(0, line3.IndexOf('#'));
                        }
                        addressLineItems = line3.Split(new char[] { ' ' });
                        if (regex.IsMatch(addressLineItems[0]))
                        {
                            addressLines += " " + line3.Trim();
                            addressLines = addressLines.Trim().TrimEnd('.');
                        }
                    }

                    string url = "http://dev.virtualearth.net/REST/v1/Locations/US/WA" + "/" + item.ZipCode.Substring(0, zipIndex).Trim() + "/" + HttpUtility.UrlPathEncode(item.City.Trim()) + "/" + HttpUtility.UrlPathEncode(addressLines) + "?output=xml&key=ArS61oV6LlNm3AonhpkkmUBXZep0rcA3UgiBVu-cNIeUl---sT0-5PY2Kcgm5r_1";
                   
                    HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest;
                    try
                    {
                        using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
                        {
                            XmlDocument xmlDoc = new XmlDocument();
                            xmlDoc.Load(response.GetResponseStream());
                            XmlNamespaceManager nsmgr = new XmlNamespaceManager(xmlDoc.NameTable);
                            nsmgr.AddNamespace("rest", "http://schemas.microsoft.com/search/local/ws/rest/v1");

                            XmlNodeList matchCodeGoodElements = xmlDoc.SelectNodes("//rest:Location/rest:MatchCode[.='Good']/parent::node()", nsmgr);

                            foreach (XmlNode location in matchCodeGoodElements)
                            {
                                if (location.SelectSingleNode(".//rest:Confidence", nsmgr).InnerText == "High")
                                {
                                    var latitude = location.SelectSingleNode(".//rest:Point", nsmgr).FirstChild.InnerText;
                                    var longitude = location.SelectSingleNode(".//rest:Point", nsmgr).FirstChild.NextSibling.InnerText;
                                    item.Latitude = Double.Parse(latitude);
                                    item.Longitude = Double.Parse(longitude);
                                }
                            }

                        }
                    }
                    catch (Exception e)
                    {
                    }

                }
            }

            foreach (var item in archive.Neighborhoods)
            {
                Console.WriteLine(item.Name);
            }

            Dictionary<string, DataTable<ListingRow>> listingsByCategory = new Dictionary<string, DataTable<ListingRow>>();

            var groupedCategories = from listing in archive.Listings group listing by listing.Category;

            if (!System.IO.Directory.Exists("Categories"))
            {
                System.IO.Directory.CreateDirectory("Categories");
            }

            foreach (var category in groupedCategories)
            {
                var listingCategory = category.First().Category;
                listingCategory = string.IsNullOrEmpty(listingCategory) ? "Misc" : listingCategory;
                var newTable = new DataTable<ListingRow>(listingCategory);
                foreach (var l in category)
                {
                    newTable.Add(l);
                }
                var fileName = Path.Combine("Categories",listingCategory.Replace("/", "_") + ".csv");
                newTable.SaveAsCsvFile(fileName);
            }

            archive.Listings.SaveAsCsvFile("Listings.csv");
            archive.Neighborhoods.SaveAsCsvFile("Neighborhoods.csv");
        }

        //xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
        //xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
        //xmlns:rs='urn:schemas-microsoft-com:rowset'
        //xmlns:z='#RowsetSchema'

        //<z:row ListingID='13875' GID='371' Edition='2012' Content='' Line1='In The Black, LLC ' Line2='Sybers, Mario'
        //     Line3='909 5th Ave Unit 1001' strCity='Seattle' strStateProv='WA' strPostalCode='98164-2022' Latitude='' Longitude=''
        //     Email='msybers@comcast.net' Phone1='2068606850' Phone1ext='' Phone2='' Website='' Category='Accountants' NeighborhoodID=''/>
        //<z:row ListingID='14166' GID='2061' Edition='2012' Content='' Line1='Courtesy Accounting &#x26; Tax Services'
        //     Line2='Voyvodich, Peter T' Line3='4411 California Ave SW' strCity='Seattle' strStateProv='WA' strPostalCode='98116'
        //     Latitude='47.564368' Longitude='-122.386980' Email='Peter@CourtesyAccounting.com' Phone1='2069324855' Phone1ext=''
        //     Phone2='' Website='' Category='Accountants' NeighborhoodID=''/>
        //<z:row ListingID='13923' GID='6269' Edition='2012' Content='' Line1='CoHo Accounting' Line2='Cohen, Andrew  R'
        //     Line3='4616 25th Ave NE PMB 252' strCity='Seattle' strStateProv='WA' strPostalCode='98105' Latitude='' Longitude=''
        //     Email='info@cohoaccounting.com' Phone1='2068292450' Phone1ext='' Phone2='' Website='www.cohoaccounting.com' Category='Accountants'
        //     NeighborhoodID=''/>
        //<z:row ListingID='14338' GID='12211' Edition='2012' Content='' Line1='Wilson Tax and Accounting' Line2='Wilson, Scott'
        //     Line3='9057 Greenwood Ave N ' strCity='Seattle' strStateProv='WA' strPostalCode='98103' Latitude='47.695230'
        //     Longitude='-122.355550' Email='scott@wilsontaxexperts.com' Phone1='2067838822' Phone1ext='' Phone2='' Website=''
        //     Category='Accountants' NeighborhoodID=''/>

    }
}
